package com.express.util;

import com.express.common.ConfigUtils;
import com.google.common.collect.Maps;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;


public class ExcelUtil {
    /**
     * 总行数
     */
    public static int totalRows = 0;
    /**
     * 总列数
     */
    public static int totalCells = 0;
    /**
     * 错误信息
     */
    public static String errorInfo;
    /**
     * 是否保存文件
     */
    public static boolean isSaveFile = false;
    /**
     * 构造方法
     */
    public ExcelUtil() {
    }

    private static Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = Maps.newHashMap();

        // 标题样式
        CellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平对齐
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐
        titleStyle.setLocked(true); // 样式锁定
        titleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        titleFont.setFontName("微软雅黑");
        titleStyle.setFont(titleFont);
        styles.put("title", titleStyle);

        // 文件头样式
        CellStyle headerStyle = wb.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); // 前景色
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 颜色填充方式
        headerStyle.setWrapText(true);
        headerStyle.setBorderRight(BorderStyle.THIN); // 设置边界
        headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        Font headerFont = wb.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        titleFont.setFontName("微软雅黑");
        headerStyle.setFont(headerFont);
        styles.put("header", headerStyle);

        Font cellStyleFont = wb.createFont();
        cellStyleFont.setFontHeightInPoints((short) 12);
        cellStyleFont.setColor(IndexedColors.BLUE_GREY.getIndex());
        cellStyleFont.setFontName("微软雅黑");

        //正文样式
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setWrapText(true);            //自动换行
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setFont(cellStyleFont);
        styles.put("cell", cellStyle);
        return styles;
    }
    private static String imgPath = new SimpleDateFormat("yyyyMMdd").format(new Date());
    private static String filePath = ConfigUtils.getString("file.path");
    public static String path =  filePath + imgPath.substring(0,6) + "/"+imgPath+"/";
    /**
     *
     * 导出大数据<br>
     * 创建人： 周灵 <br>
     * 创建时间： 2018年1月5日 下午2:54:57
     *  * @param localHSSFWorkbook 工作簿
     *  * @param paramList 表头
     *  * @param paramList1 数据源
     *  * @param paramHttpServletResponse 响应对象
     *  * @param paramString 文件名
     */
    public static void exportBigDataExcell(SXSSFWorkbook localHSSFWorkbook, List<String> paramList, List<Object[]> paramList1, HttpServletResponse response, String paramString) {
        ServletOutputStream localServletOutputStream = null;
        try  {
            // SXSSFWorkbook localHSSFWorkbook = new SXSSFWorkbook(1000);
            Sheet localHSSFSheet = localHSSFWorkbook.createSheet();
            localHSSFWorkbook.setSheetName(0, paramString);
            Row localHSSFRow1 = localHSSFSheet.createRow(0);
            Row localHSSFRow2 = null;
            localHSSFSheet.createFreezePane(0, 1);
            CellStyle titlestyle = getTitleStyle(localHSSFWorkbook);
            CellStyle bodystyle = getbodyStyle(localHSSFWorkbook);
            int i = 0;
            int j = paramList.size();
            ((SXSSFSheet) localHSSFSheet).trackAllColumnsForAutoSizing();//手动
            for(int index=0;index<j;index++){
                localHSSFSheet.autoSizeColumn(index, true);
                localHSSFSheet.setColumnWidth(index, 4000);
            }//把列宽个设置为自动
            localHSSFRow1.setHeight((short)400);
            while (i < j) {
                createCell(titlestyle, localHSSFRow1, i, (String)paramList.get(i),false);
                i++;
            }
            i = 1;
            j = paramList1.size();
            while (i <= j) {
                localHSSFRow2 = localHSSFSheet.createRow(i);
                localHSSFRow2.setHeight((short)400);
                Object[] arrayOfObject = paramList1.get(i - 1);
                for (int k = 0; k < arrayOfObject.length; k++)
                    createCell(bodystyle, localHSSFRow2,k, arrayOfObject[k] == null ? "" : arrayOfObject[k].toString(),false);
                i++;
            }
            localServletOutputStream = response.getOutputStream();
            // 判断是否写入文件
            if(isSaveFile){
                File file = new File(new String((path + paramString+".xls").getBytes("UTF-8"), "UTF-8"));
                if (!file.getParentFile().exists()) {
                    file.getParentFile().mkdirs();
                }
                //文件流
                if (!file.exists()) {
                    file.createNewFile();
                }
                FileOutputStream output = new FileOutputStream(path + paramString+".xls");
                localHSSFWorkbook.write(output);
                return;
            }
            response.reset();
            response.addHeader("Content-Disposition", String.format("attachment;filename*=utf-8'zh_cn'%s.xls", new Object[] { URLEncoder.encode(paramString, "utf-8") }));
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/x-msdownload");
            response.flushBuffer();
            localHSSFWorkbook.write(localServletOutputStream);
            localHSSFWorkbook.close();
            localServletOutputStream.flush();
            localServletOutputStream.close();
            return;
        } catch (IOException localIOException1) {
            localIOException1.printStackTrace();
        } finally {
            try  {
                localHSSFWorkbook.close();
                if(localServletOutputStream!=null)
                    localServletOutputStream.close();
            } catch (IOException localIOException4) {
                localIOException4.printStackTrace();
            }
        }
    }
    /**
     * <br> 获取工作簿对象
     */
    public static SXSSFWorkbook getWorkBook(){
        return new SXSSFWorkbook();
    }
    /**
     * <br> 获取工作簿对象*
     */
    public static SXSSFWorkbook getWorkBook(int a){
        return new SXSSFWorkbook(a);
    }
    /**
     *
     * 获取表头样式，主要设置字体背景色(#CCC)<br>
     * 创建人： 周灵 <br>
     * 创建时间： 2018年2月22日 下午1:27:02
     *  * @param Workbook
     *  * @return
     */
    private  static CellStyle getTitleStyle(SXSSFWorkbook Workbook){
        CellStyle localHSSFCellStyle = Workbook.createCellStyle();
        localHSSFCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        localHSSFCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        localHSSFCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        localHSSFCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        localHSSFCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        localHSSFCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#.###"));

        //------------------字体设置
        Font headerFont = Workbook.createFont() ;//创建字体样式
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontName("微软雅黑");  //设置字体类型
        headerFont.setFontHeightInPoints((short) 11);    //设置字体大小
        localHSSFCellStyle.setFont(headerFont); //设置字体样式
        localHSSFCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        localHSSFCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        return localHSSFCellStyle;

    }
    /**
     *
     * 主体样式内容<br>
     * <br>
     * 创建人： 周灵 <br>
     * 创建时间： 2018年2月22日 下午1:27:36
     *  * @param Workbook
     *  * @return
     */
    private  static CellStyle getbodyStyle(SXSSFWorkbook Workbook){
        CellStyle localHSSFCellStyle = Workbook.createCellStyle();
        localHSSFCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直对齐
        localHSSFCellStyle.setBorderBottom(CellStyle.BORDER_THIN);//设置边框
        localHSSFCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        localHSSFCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        localHSSFCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        localHSSFCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#.###"));
        localHSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
        localHSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        localHSSFCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        localHSSFCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        localHSSFCellStyle.setWrapText(true);            //自动换行
        localHSSFCellStyle.setBorderRight(BorderStyle.THIN);
        localHSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        localHSSFCellStyle.setBorderLeft(BorderStyle.THIN);
        localHSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        localHSSFCellStyle.setBorderTop(BorderStyle.THIN);
        localHSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        localHSSFCellStyle.setBorderBottom(BorderStyle.THIN);
        localHSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        //------------------字体设置
        Font headerFont = Workbook.createFont() ;//创建字体样式
//        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体
        headerFont.setFontName("宋体");  //设置字体类型
        headerFont.setFontHeightInPoints((short) 11);    //设置字体大小
        localHSSFCellStyle.setFont(headerFont);
        return localHSSFCellStyle;

    }
    //创建单元格样式
    private static void createCell(CellStyle localHSSFCellStyle,Row paramHSSFRow, int paramShort, String paramString,boolean num) {
        Cell localHSSFCell = paramHSSFRow.createCell(paramShort);
        localHSSFCellStyle.setAlignment((short)6);
        localHSSFCell.setCellStyle(localHSSFCellStyle);
        if (paramString == null)
            localHSSFCell.setCellValue("");
        else{
            if(num&&!"".equals(paramString)){
                localHSSFCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                localHSSFCell.setCellValue(Double.valueOf(paramString));
            }else{
                localHSSFCell.setCellValue(paramString);
                localHSSFCell.setCellType(1);
            }
        }
    }
    /**
     * 检查是2003 还是2007版本
     *
     * @param filePath
     * @return
     */
    public static boolean validateExcel(String filePath) {
        try {
            /** 检查文件名是否为空或者是否是Excel格式的文件 */
            if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
                System.out.print("文件名不是excel格式");
                return false;
            }
            /** 检查文件是否存在 */
            File file = new File(filePath);
            if (file == null || !file.exists()) {
                System.out.print("文件不存在");
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return true;
    }

    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
   /**
    * @Author zhoul
    * @Description 读取excel文件
    * @Date 2019/4/12
    * @Param [filePath 文件路径, isWrite 是否反写, map 数据源]
    * @return java.util.List<java.util.List<java.lang.String>>
    **/
    public static List<List<String>> read(String filePath,boolean isWrite,List<Map<String,Object>> list) {
        List<List<String>> dataLst = new ArrayList<List<String>>();
        InputStream is = null;
        try {
            /** 验证文件是否合法 */
            if (!ExcelUtil.validateExcel(filePath)) {
                return null;
            }
            /** 判断文件的类型，是2003还是2007 */
            boolean isExcel2003 = true;
            if (isExcel2007(filePath)) {
                isExcel2003 = false;
            }
            /** 调用本类提供的根据流读取的方法 */
            File file = new File(filePath);
            is = new FileInputStream(file);
            if(isWrite && list!=null){
                dataLst = ExcelUtil.read(is, isExcel2003, file,list);
            } else {
                dataLst = ExcelUtil.read(is, isExcel2003, file);
            }
            is.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    e.printStackTrace();
                }
            }
        }
        /** 返回最后读取的结果 */
        return dataLst;
    }

    /**
     * 读取excel文件
     * @param inputStream isExcel2003 file
     * @return
     */
    public static List<List<String>> read(InputStream inputStream, boolean isExcel2003, File file) {
        List<List<String>> dataLst = null;
        try {
            /** 根据版本选择创建Workbook的方式 */
            Workbook wb = null;
            if (isExcel2003) {
                wb = new HSSFWorkbook(inputStream);
            } else {
                wb = new XSSFWorkbook(inputStream);
            }
            dataLst = ExcelUtil.read(wb, file);
        } catch (IOException e) {

            e.printStackTrace();
        }
        return dataLst;
    }
    /**
     * 读取excel文件
     * @param inputStream isExcel2003 file
     * @return
     */
    public static List<List<String>> read(InputStream inputStream, boolean isExcel2003, File file,List<Map<String,Object>> list) {
        List<List<String>> dataLst = null;
        try {
            /** 根据版本选择创建Workbook的方式 */
            Workbook wb = null;
            if (isExcel2003) {
                wb = new HSSFWorkbook(inputStream);
            } else {
                wb = new XSSFWorkbook(inputStream);
            }
            dataLst = ExcelUtil.read(wb, file,list);
        } catch (IOException e) {

            e.printStackTrace();
        }
        return dataLst;
    }
    /**
     * 读取excel文件
     * @param  wb
     * @param  file
     * @return
     */
    private static List<List<String>> read(Workbook wb, File file) {
        try {
            List<List<String>> dataLst = new ArrayList<List<String>>();
            /** 得到第一个shell */
            Sheet sheet = wb.getSheetAt(0);
            /** 得到Excel的行数 */
            ExcelUtil.totalRows = sheet.getPhysicalNumberOfRows();
            /** 得到Excel的列数 */
            if (ExcelUtil.totalRows >= 1 && sheet.getRow(0) != null) {
                ExcelUtil.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
            }
            //获取当前日期
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd");
            String currenDate = sdf.format(new Date());
            boolean isSave = false;//是否保存工作文件
            /** 循环Excel的行 */
            for (int r = 0; r < ExcelUtil.totalRows; r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                String tempDate = "";
                List<String> rowLst = new ArrayList<String>();
                /** 循环Excel的列 */
                for (int c = 0; c < ExcelUtil.totalCells; c++) {
                    Cell cell = row.getCell(c);
                    String cellValue = "";
                    if (null != cell) {
                        // 以下是判断数据的类型
                        switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    cellValue = sdf.format(date);
                                } else if (c == 0) {//日期操作
                                    tempDate = sdf.format(cell.getDateCellValue());
                                } else {
                                    cellValue = cell.getNumericCellValue() + "";
                                }
                                break;
                            case HSSFCell.CELL_TYPE_STRING: // 字符串
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                cellValue = cell.getBooleanCellValue() + "";
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA: // 公式
//                            cellValue = cell.getCellFormula() + "";
                                try {
                                    cellValue = cell.getStringCellValue();
                                } catch (IllegalStateException e) {
                                    cellValue = String.valueOf(cell.getNumericCellValue());
                                }
                                break;
                            case HSSFCell.CELL_TYPE_BLANK: // 空值
                                cellValue = "";
                                break;
                            case HSSFCell.CELL_TYPE_ERROR: // 故障
                                cellValue = "非法字符";
                                break;
                            default:
                                cellValue = "未知类型";
                                break;
                        }
                        rowLst.add(cellValue);
                    }
                }
                /** 保存第r行的第c列 */
                dataLst.add(rowLst);
            }
            return dataLst;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
   /**
    * @Author zhoul
    * @Description 读取并反写
    * @Date 2019/4/12
    * @Param [wb, file, map]
    * @return java.util.List<java.util.List<java.lang.String>>
    **/
    private static List<List<String>> read(Workbook wb, File file,List<Map<String,Object>> list) {
        try {
            List<List<String>> dataLst = new ArrayList<List<String>>();
            /** 得到第一个shell */
            Sheet sheet = wb.getSheetAt(0);
            /** 得到Excel的行数 */
            ExcelUtil.totalRows = sheet.getPhysicalNumberOfRows();
            /** 得到Excel的列数 */
            if (ExcelUtil.totalRows >= 1 && sheet.getRow(0) != null) {
                ExcelUtil.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
            }
            //获取当前日期
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd");
            String currenDate = sdf.format(new Date());
            boolean isSave = false;//是否保存工作文件
            int total=0,index = 0;
            String group = "";//组名称
            String name = "";//坐席名称
            Map<Integer,Object> resMap = initMap();
            /** 循环Excel的行 */
            for (int r = 0; r < ExcelUtil.totalRows; r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                String tempDate = "";
                List<String> rowLst = new ArrayList<String>();
                /** 循环Excel的列 */
                for (int c = 0; c < ExcelUtil.totalCells; c++) {
                    Cell cell = row.getCell(c);
                    String cellValue = "";
                    if (null != cell) {
                        // 以下是判断数据的类型
                        switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    cellValue = sdf.format(date);
                                } else if (c == 0) {//日期操作
                                    tempDate = sdf.format(cell.getDateCellValue());
                                } else {
                                    cellValue = cell.getNumericCellValue() + "";
                                }
                                break;
                            case HSSFCell.CELL_TYPE_STRING: // 字符串
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                cellValue = cell.getBooleanCellValue() + "";
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA: // 公式
//                            cellValue = cell.getCellFormula() + "";
                                try {
                                    cellValue = cell.getStringCellValue();
                                } catch (IllegalStateException e) {
                                    cellValue = String.valueOf(cell.getNumericCellValue());
                                }
                                break;
                            case HSSFCell.CELL_TYPE_BLANK: // 空值
                                cellValue = "";
                                break;
                            case HSSFCell.CELL_TYPE_ERROR: // 故障
                                cellValue = "非法字符";
                                break;
                            default:
                                cellValue = "未知类型";
                                break;
                        }
                        rowLst.add(cellValue);
                    }
                    //写入当前数据
                    if(r>=2 && StringUtils.isNotBlank(cellValue) ){
                        group = cellValue;
                        name = list.get(index).get("name").toString();
                        if(StringUtils.isNotBlank(name)){
                            cellValue = c>=2 ? list.get(index).get(resMap.get(c)).toString():"";
                        }
                    } else {
                       name = "";
                    }
                }
                /** 保存第r行的第c列 */
                dataLst.add(rowLst);
            }
            if(isSave){
                //保存Excel文件
                FileOutputStream fileOut;
                try {
                    fileOut = new FileOutputStream(file);
                    wb.write(fileOut);
                    fileOut.close();
                    System.out.println("保存文件成功!");
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return dataLst;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    private static Map<Integer,Object> initMap(){
        Map<Integer,Object> map = new HashMap<Integer, Object>(){{
            put(0,"name");
            put(1,"00:00-09:00");
            put(2,"09:00-12:00");
            put(3,"12:00-16:00");
            put(4,"16:00-18:30");
            put(5,"18:30-00:00");
            put(6,"sum");
        }};
        return map;
    }

    private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("0");// 格式化 number为整

    private static final DecimalFormat DECIMAL_FORMAT_PERCENT = new DecimalFormat("##.00%");//格式化分比格式，后面不足2位的用0补齐

//  private static final DecimalFormat df_per_ = new DecimalFormat("0.00%");//格式化分比格式，后面不足2位的用0补齐,比如0.00,%0.01%

//  private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); // 格式化日期字符串

    private static final FastDateFormat FAST_DATE_FORMAT = FastDateFormat.getInstance("yyyy/MM/dd");

    private static final DecimalFormat DECIMAL_FORMAT_NUMBER  = new DecimalFormat("0.00E000"); //格式化科学计数器

    private static final Pattern POINTS_PATTERN = Pattern.compile("0.0+_*[^/s]+"); //小数匹配

    /**
     * 对外提供读取excel 的方法
     * @param file
     * @return
     * @throws IOException
     */
    public static List<List<Object>> readExcel(MultipartFile file) throws IOException {
        String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();
        if(Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
            return readExcel(file.getInputStream());
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /**
     * 对外提供读取excel 的方法
     * @param file
     * @param cls
     * @return
     * @throws IOException
     */
    public static <T> List<T> readExcel(MultipartFile file, Class<T> cls) throws IOException {
        String extension = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1).toLowerCase();
        if(Objects.equals("xls", extension) || Objects.equals("xlsx", extension)) {
            return readExcel(file.getInputStream(), cls);
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /**
     * 读取 office excel
     *
     * @param inputStream
     * @return
     * @throws IOException
     */
    private static List<List<Object>> readExcel(InputStream inputStream) throws IOException {
        List<List<Object>> list = new LinkedList<>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            int sheetsNumber = workbook.getNumberOfSheets();
            for (int n = 0; n < sheetsNumber; n++) {
                Sheet sheet = workbook.getSheetAt(n);
                Object value = null;
                Row row = null;
                Cell cell = null;
                for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // 从第二行开始读取
                    row = sheet.getRow(i);
                    if (row == null || StringUtils.isBlank(row+"")) {
                        continue;
                    }
                    List<Object> linked = new LinkedList<>();
                    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                        cell = row.getCell(j);
                        if (cell ==null || StringUtils.isBlank(cell+"")) {
                            value = "";
                        } else{
                            value = getCellValue(cell,false);
                        }
                        linked.add(value);
                    }
                    list.add(linked);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(inputStream);
        }
        return list;
    }

    /**
     * 获取excel数据 将之转换成bean
     *
     * @param inputStream
     * @param colCls
     * @param <T>
     * @return
     */
    private static <T> List<T> readExcel(InputStream inputStream, Class<T> colCls) {
        List<T> dataList = new LinkedList<T>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            Map<String, List<Field>> classMap = new HashMap<String, List<Field>>();
            Field[] fields = colCls.getDeclaredFields();
            for (Field field : fields) {
                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                if (annotation != null) {
                    String value = annotation.value();
                    if (!classMap.containsKey(value)) {
                        classMap.put(value, new ArrayList<Field>());
                    }
                    field.setAccessible(true);
                    classMap.get(value).add(field);
                }
            }
            Map<Integer, List<Field>> reflectionMap = new HashMap<Integer, List<Field>>();
            int sheetsNumber = workbook.getNumberOfSheets();
            for (int n = 0; n < sheetsNumber; n++) {
                Sheet sheet = workbook.getSheetAt(n);
                for (int j = sheet.getRow(0).getFirstCellNum(); j < sheet.getRow(0).getLastCellNum(); j++) { //首行提取注解
                    Object cellValue = getCellValue(sheet.getRow(0).getCell(j),false);
                    if (classMap.containsKey(cellValue)) {
                        reflectionMap.put(j, classMap.get(cellValue));
                    }
                }
                Row row = null;
                Cell cell = null;
                for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                    row = sheet.getRow(i);
                    T t = colCls.newInstance();
                    for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                        cell = row.getCell(j);
                        if (reflectionMap.containsKey(j)) {
                            Object cellValue ;
                            if(j>=22){
                                cellValue = getCellValue(cell,true);
                            } else {
                                cellValue = getCellValue(cell,false);
                            }
                            List<Field> fieldList = reflectionMap.get(j);
                            for (Field field : fieldList) {
                                try {
                                    field.set(t, cellValue);
                                } catch (Exception e) {
                                    //logger.error()
                                }
                            }
                        }
                    }
                    dataList.add(t); }
            }
        } catch (Exception e) {
            dataList = null;
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(inputStream);
        }
        return dataList;
    }

    /**
     * 获取excel 单元格数据
     *
     * @param cell
     * @return
     * @throws ParseException
     */
    private static Object getCellValue(Cell cell,boolean isFormat) throws ParseException {
        Object value = null;
        if(cell == null ){
            return value;
        }
        switch (cell.getCellTypeEnum()) {
            case _NONE:
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            case NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)){ //日期
                    value = isFormat? DateUtils.format(DateUtil.getJavaDate(cell.getNumericCellValue()),"HH:mm:ss"):
                            FAST_DATE_FORMAT.format(DateUtil.getJavaDate(cell.getNumericCellValue()));//统一转成 yyyy/MM/dd
                } else if("@".equals(cell.getCellStyle().getDataFormatString())
                        || "General".equals(cell.getCellStyle().getDataFormatString())
                        || "0_ ".equals(cell.getCellStyle().getDataFormatString())){
                    //文本  or 常规 or 整型数值
                    value = DECIMAL_FORMAT.format(cell.getNumericCellValue());
                } else if(POINTS_PATTERN.matcher(cell.getCellStyle().getDataFormatString()).matches()){ //正则匹配小数类型
                    value = cell.getNumericCellValue();  //直接显示
                } else if("0.00E+00".equals(cell.getCellStyle().getDataFormatString())){//科学计数
                    value = cell.getNumericCellValue(); //待完善
                    value = DECIMAL_FORMAT_NUMBER.format(value);
                } else if("0.00%".equals(cell.getCellStyle().getDataFormatString())){//百分比
                    value = cell.getNumericCellValue(); //待完善
                    value = DECIMAL_FORMAT_PERCENT.format(value);
                } else if("# ?/?".equals(cell.getCellStyle().getDataFormatString())){//分数
                    value = cell.getNumericCellValue(); ////待完善
                } else { //货币
                    value = cell.getNumericCellValue();
                    value = DecimalFormat.getCurrencyInstance().format(value);
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case BLANK:
                //value = ",";
                break;
            default:
                value = cell.toString();
        }
        return value;
    }

    public static void main(String[] args) {
        try {
            //读取丢送新修改起止期 数据
//            String filePath = "D:\\uploadFile\\dsxEff.txt";
//            String filePath1 = "G:\\xiaonaWork\\丢送新\\批量开通丢送新车主必要信息_返回_20180821_V1.0.xlsx";
//            File file = new File(filePath1);
//            InputStream inputStream = new FileInputStream(file);
//            List<List<Object>> list =  ExcelUtil.readExcel(inputStream);
//            System.out.println("获取:"+list.size()+"条数据.");
//            list.remove(0);
//            for (List<Object> obj : list) {
//                EntityUtil.objectToMap(obj);
//                System.out.print(obj.size());
//            }
            //打开文件
//			Runtime.getRuntime().exec("cmd  /c  start "+excelpath);
//			String month = getDateByName(date.month);
//			System.out.println(month);
            String excelPath = "G:\\xiaonaWork\\丢送新\\email\\丢送新分组统计.xlsx";
            List<List<String>> datas = ExcelUtil.read(excelPath,false,null);
            //List<List<String>> datas = ExcelUtil.read("D:/uploadFile/hlTest.xlsx");
            for (List<String> list: datas){
                System.out.println(list.size());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
